package com.junzhiit.nengji.dao.impl;

import java.util.Date;

import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.ShopAnalysisBO;
import com.junzhiit.nengji.bo.UserSexCountBO;
import com.junzhiit.nengji.dao.ShopHistoryDAO;
import com.junzhiit.nengji.model.ShopHistory;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.ShopHistoryType;
import com.junzhiit.nengji.model.enums.UserSexType;

@Repository
public class ShopHistoryDAOImpl extends AbstractBaseDAO<ShopHistory>implements ShopHistoryDAO {

	@Override
	public ShopAnalysisBO getShopAnalysis(Date startTime, Date endTime, String systemId, Integer type) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select");
		sql.append(
				" she.shop_visit_user_count,she.shop_view_number,shi.shop_share_visit_count,shs.shop_share_number,shs.shop_share_user_count");
		sql.append(" from");
		if (type==Is.YES.toIntValue()) {
			sql.append(" (select count(distinct sh.user_id) shop_visit_user_count ");
			sql.append(" ,(case when sum(sh.number) is null then 0 when sum(sh.number) is not null then sum(sh.number) else sum(sh.number) end) shop_view_number ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time between ? and ?) she, ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.VIEW.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
			sql.append("(select count(distinct sh.user_id) shop_share_visit_count ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time between ? and ?) shi, ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.VISIT.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
			sql.append("(select count(distinct sh.user_id) shop_share_user_count ");
			sql.append(" ,(case when sum(sh.number) is null then 0 when sum(sh.number) is not null then sum(sh.number) else sum(sh.number) end) shop_share_number ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id=? and sh.type=? and sh.created_time between ? and ?) shs ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.SHARE.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
		} else {
			sql.append(" (select count(distinct sh.user_id) shop_visit_user_count ");
			sql.append(" ,(case when sum(sh.number) is null then 0 when sum(sh.number) is not null then sum(sh.number) else sum(sh.number) end) shop_view_number ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?) and sh.type=? and sh.created_time between ? and ?) she, ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.VIEW.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
			sql.append("(select count(distinct sh.user_id) shop_share_visit_count ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?) and sh.type=? and sh.created_time between ? and ?) shi, ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.VISIT.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
			sql.append("(select count(distinct sh.user_id) shop_share_user_count ");
			sql.append(" ,(case when sum(sh.number) is null then 0 when sum(sh.number) is not null then sum(sh.number) else sum(sh.number) end) shop_share_number ");
			sql.append(" from shop_history sh where sh.is_delete=? and sh.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?) and sh.type=? and sh.created_time between ? and ?) shs ");
			params.setInt(IsDelete.NO.toIntValue());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
			params.setInt(ShopHistoryType.SHARE.toIntValue());
			params.setDate(startTime);
			params.setDate(endTime);
		}
		return query(sql.toString(), ShopAnalysisBO.class, params);
	}

	@Override
	public UserSexCountBO getUserSex(Date sTime, Date eTime, String systemId,Integer type) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" mu.man,wu.woman,uu.unknown");
		sql.append(" from");
		sql.append("(select count(u.id) man from user u inner join orders o on u.id=o.user_id where u.is_delete=? and u.sex=? and u.created_time between ? and ?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(UserSexType.MAN.toIntValue());
		params.setDate(sTime);
		params.setDate(eTime);
		if(type==Is.YES.toIntValue()){
			sql.append(" and o.shop_id=?) mu,");
			params.setString(systemId);
		}else{
			sql.append(" and o.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?)) mu,");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
		}
		sql.append("(select count(u.id) woman from user u inner join orders o on u.id=o.user_id where u.is_delete=? and u.sex=? and u.created_time between ? and ?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(UserSexType.WOMAN.toIntValue());
		params.setDate(sTime);
		params.setDate(eTime);
		if(type==Is.YES.toIntValue()){
			sql.append(" and o.shop_id=?) wu,");
			params.setString(systemId);
		}else{
			sql.append(" and o.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?)) wu,");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
		}
		sql.append("(select count(u.id) unknown from user u inner join orders o on u.id=o.user_id where u.is_delete=? and u.sex=? and u.created_time between ? and ?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(UserSexType.UNKNOWN.toIntValue());
		params.setDate(sTime);
		params.setDate(eTime);
		if(type==Is.YES.toIntValue()){
			sql.append(" and o.shop_id=?) uu");
			params.setString(systemId);
		}else{
			sql.append(" and o.shop_id in (select s.id from shop s where s.is_delete=? and s.chain_id=?)) uu");
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(systemId);
		}
		return query(sql.toString(),UserSexCountBO.class,params);
	}

	@Override
	public ShopHistory getTodayByUserId(String userId, String shopId,Integer type) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from shop_history where to_days(created_time) = to_days(now()) and type = ?");
		sql.append(" and user_id =? and shop_id =?");
		StatementParameter params = new StatementParameter();
		params.setInt(type);
		params.setString(userId);
		params.setString(shopId);
		return super.query(sql.toString(),ShopHistory.class,params);
	}

}
